import pandas as pd

df = pd.read_excel('豆瓣高评分书籍信息-清洗完毕.xlsx.xlsx')

publisher_counts = df['出版社'].value_counts()


large_publishers = publisher_counts[publisher_counts > 1].index.tolist()

df_large_publishers = df[df['出版社'].isin(large_publishers)].copy()

df_large_publishers['评分'] = pd.to_numeric(df_large_publishers['评分'], errors='coerce')

publisher_avg_score = df_large_publishers.groupby('出版社')['评分'].mean().sort_values(ascending=False)

df['评论数量'] = pd.to_numeric(df['评论数量'], errors='coerce')
df['评分'] = pd.to_numeric(df['评分'], errors='coerce')
df_high_comments = df[df['评论数量'] > 50000].copy()
df_high_comments_sorted = df_high_comments.sort_values(by='评分', ascending=False)

avg_score_high_comments = df_high_comments_sorted['评分'].mean()

with pd.ExcelWriter('豆瓣高评分书籍信息_分析结果.xlsx') as writer:

    publisher_counts.to_frame('作品数量').to_excel(writer, sheet_name='出版社作品数量')

    publisher_avg_score.to_frame('平均评分').to_excel(writer, sheet_name='大出版社平均分')

    df_high_comments_sorted.to_excel(writer, sheet_name='高评论作品', index=False)

    pd.DataFrame({'高评论作品评分平均值': [avg_score_high_comments]}).to_excel(writer, sheet_name='高评论评分均值', index=False)

print("分析完成，结果已保存到 '豆瓣高评分书籍信息_分析结果.xlsx'")

df = pd.read_excel('豆瓣高评分书籍信息-清洗完毕.xlsx.xlsx')

df['评论数量'] = pd.to_numeric(df['评论数量'], errors='coerce')
df['评分'] = pd.to_numeric(df['评分'], errors='coerce')

df_filtered = df[df['评论数量'] > 100]

df_filtered = df_filtered[df_filtered['评分'] >= 8]

author_counts = df_filtered['作者'].value_counts()

excellent_authors = author_counts[author_counts >= 2].index.tolist()

df_excellent_authors = df_filtered[df_filtered['作者'].isin(excellent_authors)].copy()

author_avg_score = df_excellent_authors.groupby('作者')['评分'].mean().sort_values(ascending=False)

author_avg_score = author_avg_score.to_frame('平均得分')

author_avg_score['优秀作品数量'] = df_excellent_authors['作者'].value_counts()

author_avg_score.to_excel('优秀作家及其平均得分.xlsx')

print("已生成优秀作家及其平均得分表格：优秀作家及其平均得分.xlsx")